from django.db.models import Avg, Sum, FloatField, Min, Max, Count
from django.shortcuts import render
import models
# Create your views here.


def getBook_statistic():
    result = models.Book.objects.aggregate(average_price=Avg('price'))
    return result


# def getBook_f():
#     result = models.Book.objects.all().aggregate(
#         price_per_page = Sum(F('price')/F('pages'), output_field=FloatField())
#     )

def get_store():
    result = models.Store.objects.aggregate(min_price=Min('book_price'), max_price=Max('book_price'))
    return result

def get_publisher():
    result = models.Publisher.objects.annotate(num_books=Count('book'))
    num_books = result[0].num_books
    return result

def get_bookrating():
    ratings = models.Author.objects.annotate(average_rating=Avg('book__rating'))

#3
#aggregate 和 annotate的差别
#aggregate主要是本表字段的汇聚计算,  annotate是外键关联汇聚计算


#4
"""
汇聚与其他查询条件如filter
"""

def distinct_filter():
    result = models.Book.objects.annotate(Count('authors',distinct=True),Count('store',distinct=True))
    count0 = result[0].authors__count
    return result

"""
    汇聚与filter
    汇聚计算与filter运算会按照书写顺序进行运算
"""
def annotate_filter():
    a, b  = models.Publisher.objects.annotate(num_books=Count('book',distinct=True)).filter(book__rating__gt=3.0)
    print a.num_books
    print b.num_books
    return a,b

"""
    条件在前,annotate在过滤后的结果集上统计
"""
def annotate_fileter2():
    a, b = models.Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
    print a, a.num_books
    print b, b.num_books
    return a,b

"""
    5 annotate和aggregate串联
"""
def annotate_aggregate():
    result = models.Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
    return result






